package com.kingree.jdbc;
123
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.swing.JButton;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JTextField;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;

public class DBUtiles extends JFrame {
	private String aa;
	private static final long serialVersionUID = 1L;
	private String path;// 导出路径
	private String txtName;// 导出文件名
	private String errorInfo;// 连接错误提示
	@SuppressWarnings("unchecked")
	private List DbOneLinkList = null;// One库的建库连接
	@SuppressWarnings("unchecked")
	private List DbTwoLinkList = null;// Two库的建库连接

	@SuppressWarnings("unchecked")
	public Map checkDataBase(List dbLinkList) {
		String addr = dbLinkList.get(0).toString();
		String username = dbLinkList.get(1).toString();
		String password = dbLinkList.get(2).toString();
		String dbName = dbLinkList.get(3).toString();
		String port = dbLinkList.get(4).toString();
		List table = new ArrayList();// 表名
		List field;// 字段名
		List value;// 大list
		List attr; // 属性值list
		Map fieldAttr; // key字段名，value是属性值list。
		Map<String, List> map = new HashMap<String, List>(); // 键是表名，值是字段list
		String url = "jdbc:mysql://" + addr + ":" + port + "/information_schema";
		String driver = "com.mysql.jdbc.Driver";
		String sqlstr;// 查询表名的sql
		String sqlfield;// 查询字段的sql
		String sqlDataType;// 查询字段类型的sql
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(url, username, password);
			stmt = con.createStatement();
			sqlstr = "SELECT TABLE_NAME FROM information_schema.`TABLES` WHERE table_schema = '" + dbName + "'ORDER BY table_name DESC";
			rs = stmt.executeQuery(sqlstr);
			while (rs.next()) {
				for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
					table.add(rs.getString(i + 1)); // table中放得是表名
				}
			}
			for (int i = 0; i < table.size(); i++) { // 循环每一个表，将表名作为map的KEY，表的字段名放到list中作为value。
				sqlfield = "SELECT COLUMN_NAME FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA ='" + dbName + "'AND TABLE_NAME ='" + table.get(i) + "'";
				rs = stmt.executeQuery(sqlfield);
				field = new ArrayList();
				value = new ArrayList();

				while (rs.next()) {
					for (int j = 0; j < rs.getMetaData().getColumnCount(); j++) {
						field.add(rs.getString(j + 1)); // field中放的是一个表的字段名
					}
				}
				for (int j = 0; j < field.size(); j++) {
					fieldAttr = new HashMap();
					attr = new ArrayList();// 属性list
					sqlDataType = "SELECT COLUMN_TYPE,IS_NULLABLE ,COLUMN_KEY FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA = '" + dbName + "'AND TABLE_NAME = '"
							+ table.get(i) + "'AND COLUMN_NAME = '" + field.get(j) + "'";
					rs = stmt.executeQuery(sqlDataType);
					while (rs.next()) {
						for (int k = 0; k < rs.getMetaData().getColumnCount(); k++) {
							attr.add(rs.getString(k + 1));// 给属性list赋值，内容[varchar,bigint,null].
							// System.out.println(rs.getString(k + 1) + "字段属性");
						}
					}
					fieldAttr.put(field.get(j), attr);// 一个字段，对应一个属性list
					value.add(fieldAttr);
				}
				map.put((String) table.get(i), value);
			}
		} catch (ClassNotFoundException e1) {
			System.out.println("数据库驱动不存在！");
		} catch (SQLException e2) {
			errorInfo = "数据库连接出错了！";
			System.out.println("数据库连接出错了！");
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				System.out.println("查询数据出错了！");
			}
		}
		return map;
	}

	@SuppressWarnings("unchecked")
	public List tableSql(String sql, String addr, String dbName, String username, String password, String port) {
		String url = "jdbc:mysql://" + addr + ":" + port + "/" + dbName;
		String driver = "com.mysql.jdbc.Driver";
		String createTavleSql = "";
		List resultList = new ArrayList();
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(url, username, password);
			stmt = con.createStatement();
			stmt.addBatch("use plannew;");
			rs = stmt.executeQuery(sql);

			while (rs.next()) {
				for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
					createTavleSql = rs.getString(i + 1);
					resultList.add(createTavleSql);
				}
			}
		} catch (ClassNotFoundException e1) {
			System.out.println("数据库驱动不存在！");
		} catch (SQLException e2) {
			errorInfo = "数据库连接出错了！";
			System.out.println("数据库连接出错了！");
			System.out.println(e2);
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				System.out.println("查询数据出错了！");
			}
		}
		return resultList;
	}

	@SuppressWarnings("unchecked")
	public void Swing() {
		final JFrame mainFrame = new JFrame("Mysql表结构对比工具");
		final JPanel jpanel = new JPanel();
		mainFrame.setResizable(false);
		mainFrame.setBounds(300, 100, 830, 460);
		mainFrame.add(jpanel);

		JButton button = new JButton("导出Excel");
		JButton button1 = new JButton("导出txt");
		JButton button2 = new JButton("快速填写");
		JButton buttonA = new JButton("以此为标准导出sql");
		JButton buttonB = new JButton("以此为标准导出sql");
		final JTextField jAddr = new JTextField();
		final JTextField jUser = new JTextField();
		final JTextField jPassword = new JTextField();
		final JTextField jDbName = new JTextField();
		final JTextField jDbPort = new JTextField();

		final JTextField jAddr2 = new JTextField();
		final JTextField jUser2 = new JTextField();
		final JTextField jPassword2 = new JTextField();
		final JTextField jDbName2 = new JTextField();
		final JTextField jDbPort2 = new JTextField();

		jAddr2.setColumns(8);
		jUser2.setColumns(6);
		jPassword2.setColumns(6);
		jDbName2.setColumns(6);
		jDbPort2.setColumns(6);

		jAddr.setColumns(8);
		jUser.setColumns(6);
		jPassword.setColumns(6);
		jDbName.setColumns(6);
		jDbPort.setColumns(6);
		jpanel.add(new JLabel("数据库地址:"));
		jpanel.add(jAddr);
		jpanel.add(new JLabel("数据库名:"));
		jpanel.add(jDbName);
		jpanel.add(new JLabel("端口:"));
		jpanel.add(jDbPort);
		jpanel.add(new JLabel("用户名:"));
		jpanel.add(jUser);
		jpanel.add(new JLabel("密码:"));
		jpanel.add(jPassword);
		jpanel.add(buttonA);

		jpanel.add(new JLabel("数据库地址:"));
		jpanel.add(jAddr2);
		jpanel.add(new JLabel("数据库名:"));
		jpanel.add(jDbName2);
		jpanel.add(new JLabel("端口:"));
		jpanel.add(jDbPort2);
		jpanel.add(new JLabel("用户名:"));
		jpanel.add(jUser2);
		jpanel.add(new JLabel("密码:"));
		jpanel.add(jPassword2);
		jpanel.add(buttonB);
		jpanel.add(button);
		jpanel.add(button1);
		jpanel.add(button2);
		/** 两个比较库的链接信息--开始 **/

		/** 两个比较库的链接信息 --结束 **/
		mainFrame.setVisible(true);

		// button对比导出excel
		button.addActionListener(new ActionListener() {

			@Override
			public void actionPerformed(ActionEvent e) {
				JFileChooser jfc = new JFileChooser();
				jfc.setDialogTitle("选择一个目录");
				jfc.setDialogType(JFileChooser.SAVE_DIALOG);// 弹出的对话框的类型为保存
				jfc.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);// 以允许用户只选择文件、只选择目录，或者可选择文件和目录,这里只能选择目录
				int res = jfc.showSaveDialog(null);// 参数该对话框的父组件，可以为null,返回值该文件选择器被弹下时的返回状态
				if (res == JFileChooser.APPROVE_OPTION) {// 选择确认后返回该值0
					File dir = jfc.getSelectedFile();
					path = dir.getAbsolutePath();
				}
				if (res == JFileChooser.CANCEL_OPTION) {// 选择取消或者关闭后返回该值1
					return;
				}
				Map dbOne = new HashMap<String, List>();
				Map dbTwo = new HashMap<String, List>();
				DbOneLinkList = new ArrayList();
				DbTwoLinkList = new ArrayList();
				String addr = jAddr.getText();
				String username = jUser.getText();
				String password = jPassword.getText();
				String dbName = jDbName.getText();
				String dbPort = jDbPort.getText();

				DbOneLinkList.add(addr);
				DbOneLinkList.add(username);
				DbOneLinkList.add(password);
				DbOneLinkList.add(dbName);
				DbOneLinkList.add(dbPort);

				String addr2 = jAddr2.getText();
				String username2 = jUser2.getText();
				String password2 = jPassword2.getText();
				String dbName2 = jDbName2.getText();
				String dbPort2 = jDbPort2.getText();

				DbTwoLinkList.add(addr2);
				DbTwoLinkList.add(username2);
				DbTwoLinkList.add(password2);
				DbTwoLinkList.add(dbName2);
				DbTwoLinkList.add(dbPort2);
				dbOne = checkDataBase(DbOneLinkList);
				dbTwo = checkDataBase(DbTwoLinkList);
				if (errorInfo != null) {
					JOptionPane.showMessageDialog(mainFrame, errorInfo);
					errorInfo = null;
					return;
				}
				txtName = DbOneLinkList.get(3).toString() + "--" + DbTwoLinkList.get(3).toString();
				List resList = compareDB(dbOne, dbTwo);
				exportExcel(resList);

			}
		});
		// button1对比导出txt
		button1.addActionListener(new ActionListener() {

			@Override
			public void actionPerformed(ActionEvent e) {
				JFileChooser jfc = new JFileChooser();
				jfc.setDialogTitle("选择一个目录");
				jfc.setDialogType(JFileChooser.SAVE_DIALOG);// 弹出的对话框的类型为保存
				jfc.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);// 以允许用户只选择文件、只选择目录，或者可选择文件和目录,这里只能选择目录
				int res = jfc.showSaveDialog(null);// 参数该对话框的父组件，可以为null,返回值该文件选择器被弹下时的返回状态
				if (res == JFileChooser.APPROVE_OPTION) {// 选择确认后返回该值0
					File dir = jfc.getSelectedFile();
					path = dir.getAbsolutePath();
					System.out.println(path);
				}
				if (res == JFileChooser.CANCEL_OPTION) {// 选择取消或者关闭后返回该值1
					return;
				}
				Map dbOne = new HashMap<String, List>();
				Map dbTwo = new HashMap<String, List>();
				DbOneLinkList = new ArrayList();
				DbTwoLinkList = new ArrayList();
				String addr = jAddr.getText();
				String username = jUser.getText();
				String password = jPassword.getText();
				String dbName = jDbName.getText();
				String dbPort = jDbPort.getText();

				DbOneLinkList.add(addr);
				DbOneLinkList.add(username);
				DbOneLinkList.add(password);
				DbOneLinkList.add(dbName);
				DbOneLinkList.add(dbPort);

				String addr2 = jAddr2.getText();
				String username2 = jUser2.getText();
				String password2 = jPassword2.getText();
				String dbName2 = jDbName2.getText();
				String dbPort2 = jDbPort2.getText();

				DbTwoLinkList.add(addr2);
				DbTwoLinkList.add(username2);
				DbTwoLinkList.add(password2);
				DbTwoLinkList.add(dbName2);
				DbTwoLinkList.add(dbPort2);
				dbOne = checkDataBase(DbOneLinkList);
				dbTwo = checkDataBase(DbTwoLinkList);
				if (errorInfo != null) {
					JOptionPane.showMessageDialog(mainFrame, errorInfo);
					errorInfo = null;
					return;
				}
				txtName = DbOneLinkList.get(3).toString() + "--" + DbTwoLinkList.get(3).toString();
				List resList = compareDB(dbOne, dbTwo);
				exportTxt(resList);

			}

		});
		// button2快速填写
		button2.addActionListener(new ActionListener() {
			@Override
			public void actionPerformed(ActionEvent e) {
				jAddr2.setText(jAddr.getText());
				jDbName2.setText(jDbName.getText());
				jDbPort2.setText(jDbPort.getText());
				jUser2.setText(jUser.getText());
				jPassword2.setText(jPassword.getText());
			}
		});

		// 以A库为标准导出sql脚本
		buttonA.addActionListener(new ActionListener() {

			public void actionPerformed(ActionEvent e) {

				JFileChooser jfc = new JFileChooser();
				jfc.setDialogTitle("选择一个目录");
				jfc.setDialogType(JFileChooser.SAVE_DIALOG);// 弹出的对话框的类型为保存
				jfc.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);// 以允许用户只选择文件、只选择目录，或者可选择文件和目录,这里只能选择目录
				int res = jfc.showSaveDialog(null);// 参数该对话框的父组件，可以为null,返回值该文件选择器被弹下时的返回状态
				if (res == JFileChooser.APPROVE_OPTION) {// 选择确认后返回该值0
					File dir = jfc.getSelectedFile();
					path = dir.getAbsolutePath();
				}
				if (res == JFileChooser.CANCEL_OPTION) {// 选择取消或者关闭后返回该值1
					return;
				}
				Map dbOne = new HashMap<String, List>();
				Map dbTwo = new HashMap<String, List>();
				DbOneLinkList = new ArrayList();
				DbTwoLinkList = new ArrayList();
				String addr = jAddr.getText();
				String username = jUser.getText();
				String password = jPassword.getText();
				String dbName = jDbName.getText();
				String dbPort = jDbPort.getText();

				DbOneLinkList.add(addr);
				DbOneLinkList.add(username);
				DbOneLinkList.add(password);
				DbOneLinkList.add(dbName);
				DbOneLinkList.add(dbPort);

				String addr2 = jAddr2.getText();
				String username2 = jUser2.getText();
				String password2 = jPassword2.getText();
				String dbName2 = jDbName2.getText();
				String dbPort2 = jDbPort2.getText();

				DbTwoLinkList.add(addr2);
				DbTwoLinkList.add(username2);
				DbTwoLinkList.add(password2);
				DbTwoLinkList.add(dbName2);
				DbTwoLinkList.add(dbPort2);
				dbOne = checkDataBase(DbOneLinkList);
				dbTwo = checkDataBase(DbTwoLinkList);
				if (errorInfo != null) {
					JOptionPane.showMessageDialog(mainFrame, errorInfo);
					errorInfo = null;
					return;
				}
				txtName = "" + dbName2 + "Sql";
				List resList = compareDB(dbOne, dbTwo);
				exportSqlDbOne(resList, DbOneLinkList, DbTwoLinkList);
			}
		});
		buttonB.addActionListener(new ActionListener() {

			public void actionPerformed(ActionEvent e) {
				JFileChooser jfc = new JFileChooser();
				jfc.setDialogTitle("选择一个目录");
				jfc.setDialogType(JFileChooser.SAVE_DIALOG);// 弹出的对话框的类型为保存
				jfc.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);// 以允许用户只选择文件、只选择目录，或者可选择文件和目录,这里只能选择目录
				int res = jfc.showSaveDialog(null);// 参数该对话框的父组件，可以为null,返回值该文件选择器被弹下时的返回状态
				if (res == JFileChooser.APPROVE_OPTION) {// 选择确认后返回该值0
					File dir = jfc.getSelectedFile();
					path = dir.getAbsolutePath();
				}
				if (res == JFileChooser.CANCEL_OPTION) {// 选择取消或者关闭后返回该值1
					return;
				}
				Map dbOne = new HashMap<String, List>();
				Map dbTwo = new HashMap<String, List>();
				DbOneLinkList = new ArrayList();
				DbTwoLinkList = new ArrayList();
				String addr = jAddr.getText();
				String username = jUser.getText();
				String password = jPassword.getText();
				String dbName = jDbName.getText();
				String dbPort = jDbPort.getText();

				DbOneLinkList.add(addr);
				DbOneLinkList.add(username);
				DbOneLinkList.add(password);
				DbOneLinkList.add(dbName);
				DbOneLinkList.add(dbPort);

				String addr2 = jAddr2.getText();
				String username2 = jUser2.getText();
				String password2 = jPassword2.getText();
				String dbName2 = jDbName2.getText();
				String dbPort2 = jDbPort2.getText();

				DbTwoLinkList.add(addr2);
				DbTwoLinkList.add(username2);
				DbTwoLinkList.add(password2);
				DbTwoLinkList.add(dbName2);
				DbTwoLinkList.add(dbPort2);
				dbOne = checkDataBase(DbOneLinkList);
				dbTwo = checkDataBase(DbTwoLinkList);
				if (errorInfo != null) {
					JOptionPane.showMessageDialog(mainFrame, errorInfo);
					errorInfo = null;
					return;
				}
				txtName = "" + dbName + "Sql";
				List resList = compareDB(dbOne, dbTwo);
				exportSqlDbTwo(resList, DbOneLinkList, DbTwoLinkList);
			}
		});
	}

	@SuppressWarnings("unchecked")
	public void exportSqlDbOne(List resList, List DbOneLinkList, List DbTwoLinkList) {
		String addr = DbOneLinkList.get(0).toString();
		String username = DbOneLinkList.get(1).toString();
		String password = DbOneLinkList.get(2).toString();
		String dbName = DbOneLinkList.get(3).toString();
		String dbPort = DbOneLinkList.get(4).toString();
		String addrTwo = DbTwoLinkList.get(0).toString();
		String dbNameTwo = DbTwoLinkList.get(3).toString();
		String dbPortTwo = DbTwoLinkList.get(4).toString();
		List diffTableOne = (List) resList.get(0);
		Map mapOne = (Map) resList.get(2);
		Map mapTwo = (Map) resList.get(3);
		Map tablefield = (Map) resList.get(4);
		try {
			FileOutputStream out = new FileOutputStream(path + "\\" + txtName + ".txt");
			SimpleDateFormat DateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm");
			String Server = "-- Source Server         : " + addrTwo + "";
			String Host = "-- Source Host           : " + addrTwo + ":" + dbPortTwo + "";
			String Database = "-- Source Database       : " + dbNameTwo + "";
			String Target = "-- Target Server Type    : MYSQL";
			String Date = "-- Date: " + DateFormat.format(new Date()) + "";
			out.write(Server.getBytes());
			out.write("\r\n".getBytes());
			out.write(Host.getBytes());
			out.write("\r\n".getBytes());
			out.write(Database.getBytes());
			out.write("\r\n".getBytes());
			out.write(Target.getBytes());
			out.write("\r\n".getBytes());
			out.write(Date.getBytes());
			out.write("\r\n\r\n".getBytes());
			String title = "-- Table structure for " + dbNameTwo;
			out.write(title.getBytes());
			out.write("\r\n\r\n".getBytes());
			// 表结构差异,one为主
			for (int i = 0; i < diffTableOne.size(); i++) {
				String sql = "show create table " + diffTableOne.get(i).toString().toUpperCase() + ";";
				List createTavleSql = tableSql(sql, addr, dbName, username, password, dbPort);// 查询当前表的建表语句
				String res = createTavleSql.get(1).toString() + ";";
				out.write(res.getBytes());
				out.write("\r\n\r\n".getBytes());
			}
			out.write("\r\n".getBytes());
			out.write("-- Field".getBytes());
			out.write("\r\n\r\n".getBytes());
			Object One[] = mapOne.keySet().toArray();
			for (int i = 0; i < One.length; i++) {
				if (mapOne.get(One[i].toString()) != null) {
					List list = (List) mapOne.get(One[i].toString());
					for (int j = 0; j < list.size(); j++) {

						String sql = "SHOW FULL FIELDS FROM " + One[i].toString().toUpperCase() + " WHERE Field ='" + list.get(j).toString().toUpperCase() + "'";
						System.out.println(sql);
						List field = tableSql(sql, addr, dbName, username, password, dbPort);
						String res = "";
						if (field.get(3).toString().equals("NO")) {
							res = "alter table " + "`" + One[i].toString().toUpperCase() + "` Add column " + list.get(j) + " " + field.get(1) + " not null;";
						} else {
							res = "alter table " + "`" + One[i].toString().toUpperCase() + "` Add column " + list.get(j) + " " + field.get(1) + ";";
						}
						out.write(res.getBytes());
						out.write("\r\n".getBytes());
					}
				}
			}
			Object Two[] = mapTwo.keySet().toArray();
			for (int i = 0; i < Two.length; i++) {
				if (mapTwo.get(Two[i].toString()) != null) {
					List list = (List) mapTwo.get(Two[i].toString());
					for (int j = 0; j < list.size(); j++) {

						String res = "alter table `" + Two[i].toString().toUpperCase() + "` drop column " + list.get(j) + ";";
						out.write(res.getBytes());
						out.write("\r\n".getBytes());
					}
				}
			}
			out.write("\r\n".getBytes());
			out.write("-- Attribute".getBytes());
			out.write("\r\n\r\n".getBytes());
			Object table[] = tablefield.keySet().toArray();
			for (int i = 0; i < table.length; i++) {
				Map map = (Map) tablefield.get(table[i].toString());
				if (map != null) {
					Object fieldAppr[] = map.keySet().toArray();
					for (int j = 0; j < fieldAppr.length; j++) {
						fieldAppr[j].toString();// 字段名
						List Appr = (List) map.get(fieldAppr[j]);// 属性List
						for (int k = 0; k < Appr.size(); k++) {
							Map attr = (Map) Appr.get(k);
							Object attrName[] = attr.keySet().toArray();// attrName存放的是字段的属性名
							for (int m = 0; m < attrName.length; m++) {
								List list = (List) attr.get(attrName[m]);// 拿到属性值list第一个是one表的第二个是two表的

								String res = "";
								if (list.get(0).toString().toUpperCase().equals("NO")) {
									String sql = "SHOW FULL COLUMNS FROM " + table[i].toString().toUpperCase() + " WHERE Field = '" + fieldAppr[j].toString() + "'";
									List attrList = tableSql(sql, addr, dbName, username, password, dbPort);
									res = "alter table `" + table[i].toString().toUpperCase() + "` change " + fieldAppr[j].toString() + " " + fieldAppr[j].toString() + " "
											+ attrList.get(1) + " not null;";
								} else {
									res = "alter table `" + table[i].toString().toUpperCase() + "` change " + fieldAppr[j].toString() + " " + fieldAppr[j].toString() + " "
											+ list.get(0).toString().toUpperCase() + ";";
								}
								out.write(res.getBytes());
								out.write("\r\n".getBytes());
							}
						}
					}
				}
			}
			out.flush();
			out.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	@SuppressWarnings( { "unchecked" })
	public void exportSqlDbTwo(List resList, List DbOneLinkList, List DbTwoLinkList) {
		String addr = DbOneLinkList.get(0).toString();
		String dbName = DbOneLinkList.get(3).toString();
		String dbPort = DbOneLinkList.get(4).toString();
		String addrTwo = DbTwoLinkList.get(0).toString();
		String usernameTwo = DbTwoLinkList.get(1).toString();
		String passwordTwo = DbTwoLinkList.get(2).toString();
		String dbNameTwo = DbTwoLinkList.get(3).toString();
		String dbPortTwo = DbTwoLinkList.get(4).toString();
		List diffTableTwo = (List) resList.get(1);
		Map mapOne = (Map) resList.get(2);
		Map mapTwo = (Map) resList.get(3);
		Map tablefield = (Map) resList.get(4);
		try {
			FileOutputStream out = new FileOutputStream(path + "\\" + txtName + ".txt");
			SimpleDateFormat DateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm");
			String Server = "-- Source Server         : " + addr + "";
			String Host = "-- Source Host           : " + addr + ":" + dbPort + "";
			String Database = "-- Source Database       : " + dbName + "";
			String Target = "-- Target Server Type    : MYSQL";
			String Date = "-- Date: " + DateFormat.format(new Date()) + "";

			out.write(Server.getBytes());
			out.write("\r\n".getBytes());
			out.write(Host.getBytes());
			out.write("\r\n".getBytes());
			out.write(Database.getBytes());
			out.write("\r\n".getBytes());
			out.write(Target.getBytes());
			out.write("\r\n".getBytes());
			out.write(Date.getBytes());
			out.write("\r\n\r\n".getBytes());
			String title = "-- Table structure for " + dbName;
			out.write(title.getBytes());
			out.write("\r\n\r\n".getBytes());

			// 表结构差异,Two为主
			for (int i = 0; i < diffTableTwo.size(); i++) {
				String sql = "show create table " + diffTableTwo.get(i).toString().toUpperCase() + ";";
				List createTavleSql = tableSql(sql, addrTwo, dbNameTwo, usernameTwo, passwordTwo, dbPortTwo);
				String res = createTavleSql.get(1).toString() + ";";
				out.write(res.getBytes());
				out.write("\r\n\r\n".getBytes());
			}
			out.write("\r\n".getBytes());
			out.write("-- Field".getBytes());
			out.write("\r\n\r\n".getBytes());
			Object One[] = mapOne.keySet().toArray();
			for (int i = 0; i < One.length; i++) {
				if (mapOne.get(One[i].toString()) != null) {
					List list = (List) mapOne.get(One[i].toString());
					for (int j = 0; j < list.size(); j++) {
						String res = "alter table `" + One[i].toString().toUpperCase() + "` drop column " + list.get(j) + ";";
						out.write(res.getBytes());
						out.write("\r\n".getBytes());

					}
				}
			}
			Object Two[] = mapTwo.keySet().toArray();
			for (int i = 0; i < Two.length; i++) {
				if (mapTwo.get(Two[i].toString()) != null) {
					List list = (List) mapTwo.get(Two[i].toString());
					for (int j = 0; j < list.size(); j++) {
						String sql = "SHOW FULL FIELDS FROM " + Two[i].toString().toUpperCase() + " WHERE Field ='" + list.get(j).toString().toUpperCase() + "'";
						List field = tableSql(sql, addrTwo, dbNameTwo, usernameTwo, passwordTwo, dbPortTwo);
						String res = "";
						if (field.get(3).toString().equals("NO")) {
							res = "alter table " + "`" + Two[i].toString().toUpperCase() + "` Add column " + list.get(j) + " " + field.get(1) + " not null;";
						} else {
							res = "alter table " + "`" + Two[i].toString().toUpperCase() + "` Add column " + list.get(j) + " " + field.get(1) + ";";
						}
						out.write(res.getBytes());
						out.write("\r\n".getBytes());
					}
				}
			}
			out.write("\r\n".getBytes());
			out.write("-- Attribute".getBytes());
			out.write("\r\n\r\n".getBytes());
			Object table[] = tablefield.keySet().toArray();
			for (int i = 0; i < table.length; i++) {
				Map map = (Map) tablefield.get(table[i].toString());
				if (map != null) {
					Object fieldAppr[] = map.keySet().toArray();
					for (int j = 0; j < fieldAppr.length; j++) {
						fieldAppr[j].toString();// 字段名
						List Appr = (List) map.get(fieldAppr[j]);// 属性List
						for (int k = 0; k < Appr.size(); k++) {
							Map attr = (Map) Appr.get(k);
							Object attrName[] = attr.keySet().toArray();// attrName存放的是字段的属性名
							for (int m = 0; m < attrName.length; m++) {
								List list = (List) attr.get(attrName[m]);// 拿到属性值list第一个是one表的第二个是two表的

								String res = "";
								if (list.get(0).toString().toUpperCase().equals("NO")) {
									String sql = "SHOW FULL COLUMNS FROM " + table[i].toString().toUpperCase() + " FROM " + dbNameTwo + " WHERE Field = '"
											+ fieldAppr[j].toString() + "'";
									List attrList = tableSql(sql, addrTwo, dbNameTwo, usernameTwo, passwordTwo, dbPortTwo);
									res = "alter table `" + table[i].toString().toUpperCase() + "` change " + fieldAppr[j].toString() + " " + fieldAppr[j].toString() + " "
											+ attrList.get(1) + " not null;";
								} else {
									res = "alter table `" + table[i].toString().toUpperCase() + "` change " + fieldAppr[j].toString() + " " + fieldAppr[j].toString() + " "
											+ list.get(1).toString().toUpperCase() + ";";
								}
								out.write(res.getBytes());
								out.write("\r\n".getBytes());
							}
						}
					}
				}
			}
			out.flush();
			out.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	@SuppressWarnings( { "unchecked", "deprecation" })
	public void exportExcel(List resList) {
		String dbNameOne = DbOneLinkList.get(3).toString().toUpperCase();
		String dbNameTwo = DbTwoLinkList.get(3).toString().toUpperCase();
		List diffTableOne = (List) resList.get(0);// one表差异
		List diffTableTwo = (List) resList.get(1);// two表差异
		Map mapOne = (Map) resList.get(2);// one字段差异
		Map mapTwo = (Map) resList.get(3);// two字段差异
		Map tablefield = (Map) resList.get(4);// 数据库属性差异
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet(dbNameOne + "-->" + dbNameTwo);
		/* 样式 */
		sheet.setColumnWidth(0, 16000);
		sheet.setColumnWidth(1, 4766);
		sheet.setColumnWidth(2, 4766);
		/* 字体 */
		Font font = wb.createFont();
		font.setFontHeightInPoints((short) 13);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);// 加粗
		CellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
		style.setFont(font);

		Row row = sheet.createRow(0); // 创建第一行
		Cell cell = row.createCell(1);// 第二列
		cell.setCellValue("数据库[" + dbNameOne + "]");
		cell = row.createCell(2);// 第三列
		cell.setCellValue("数据库[" + dbNameTwo + "]");

		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 2));
		row = sheet.createRow(1);// 创建第二行
		row.setHeight((short) 400);
		cell = row.createCell(0);
		cell.setCellValue("数据库表区别");
		cell.setCellStyle(style);
		int index = 2;
		// 表结构差异,one为主
		for (int i = 0; i < diffTableOne.size(); i++, index++) {
			String res = "表[" + diffTableOne.get(i).toString().toUpperCase() + "]";
			row = sheet.createRow(index);// 创建行从第三行开始
			cell = row.createCell(0);// 第一列
			cell.setCellValue(res);// 赋值
			cell = row.createCell(1);// 第二列
			cell.setCellValue("有");// 赋值
			cell = row.createCell(2);// 第二列
			cell.setCellValue("无");// 赋值
		}
		for (int i = 0; i < diffTableTwo.size(); i++, index++) {
			String res = "表[" + diffTableTwo.get(i).toString().toUpperCase() + "]";
			row = sheet.createRow(index);// 创建行从第index行开始
			cell = row.createCell(0);// 第一列
			cell.setCellValue(res);// 赋值
			cell = row.createCell(1);// 第二列
			cell.setCellValue("无");// 赋值
			cell = row.createCell(2);// 第二列
			cell.setCellValue("有");// 赋值
		}
		sheet.addMergedRegion(new CellRangeAddress(index, index, 0, 2));
		row = sheet.createRow(index);
		row.setHeight((short) 400);
		cell = row.createCell(0);
		cell.setCellValue("数据库字段区别");
		cell.setCellStyle(style);
		index = index + 1;
		Object One[] = mapOne.keySet().toArray();
		for (int i = 0; i < One.length; i++) {
			if (mapOne.get(One[i].toString()) != null) {
				List list = (List) mapOne.get(One[i].toString());
				for (int j = 0; j < list.size(); j++, index++) {
					String res = "表[" + One[i].toString().toUpperCase() + "]  字段[" + list.get(j).toString().toUpperCase() + "]";
					row = sheet.createRow(index);// 创建行从第index行开始
					cell = row.createCell(0);// 第一列
					cell.setCellValue(res);// 赋值
					cell = row.createCell(1);// 第二列
					cell.setCellValue("有");// 赋值
					cell = row.createCell(2);// 第二列
					cell.setCellValue("无");// 赋值
				}
			}
		}
		Object Two[] = mapTwo.keySet().toArray();
		for (int i = 0; i < Two.length; i++) {
			if (mapTwo.get(Two[i].toString()) != null) {
				List list = (List) mapTwo.get(Two[i].toString());
				for (int j = 0; j < list.size(); j++, index++) {
					String res = "表[" + Two[i].toString().toUpperCase() + "]  字段[" + list.get(j).toString().toUpperCase() + "]";
					row = sheet.createRow(index);// 创建行从第index行开始
					cell = row.createCell(0);// 第一列
					cell.setCellValue(res);// 赋值
					cell = row.createCell(1);// 第二列
					cell.setCellValue("无");// 赋值
					cell = row.createCell(2);// 第二列
					cell.setCellValue("有");// 赋值
				}
			}
		}
		sheet.addMergedRegion(new CellRangeAddress(index, index, 0, 2));
		sheet.addMergedRegion(new CellRangeAddress(index, index, 0, 2));
		row = sheet.createRow(index);
		row.setHeight((short) 400);
		cell = row.createCell(0);
		cell.setCellValue("数据库字段属性区别");
		cell.setCellStyle(style);
		index = index + 1;
		Object table[] = tablefield.keySet().toArray();
		for (int i = 0; i < table.length; i++) {
			Map map = (Map) tablefield.get(table[i].toString());
			if (map != null) {
				Object fieldAppr[] = map.keySet().toArray();
				for (int j = 0; j < fieldAppr.length; j++) {
					fieldAppr[j].toString();// 字段名
					List Appr = (List) map.get(fieldAppr[j]);// 属性List
					for (int k = 0; k < Appr.size(); k++) {
						Map attr = (Map) Appr.get(k);
						Object attrName[] = attr.keySet().toArray();// attrName存放的是字段的属性名
						for (int m = 0; m < attrName.length; m++, index++) {
							List list = (List) attr.get(attrName[m]);// 拿到属性值list第一个是one表的第二个是two表的
							String res = "表[" + table[i].toString().toUpperCase() + "] 字段[" + fieldAppr[j].toString() + "] 属性[" + attrName[m] + "]";
							row = sheet.createRow(index);// 创建行
							cell = row.createCell(0);// 第一列
							cell.setCellValue(res);// 赋值
							cell = row.createCell(1);// 第二列
							cell.setCellValue(list.get(0).toString().toUpperCase());// 赋值
							cell = row.createCell(2);// 第二列
							cell.setCellValue(list.get(1).toString().toUpperCase());// 赋值
						}
					}
				}
			}
		}
		try {
			FileOutputStream out = new FileOutputStream(path + "\\" + txtName + ".xls");
			wb.write(out);
			out.flush();
			out.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

	}

	@SuppressWarnings("unchecked")
	public void exportTxt(List resList) {
		String dbNameOne = DbOneLinkList.get(3).toString().toUpperCase();
		String dbNameTwo = DbTwoLinkList.get(3).toString().toUpperCase();
		List diffTableOne = (List) resList.get(0);
		List diffTableTwo = (List) resList.get(1);
		Map mapOne = (Map) resList.get(2);
		Map mapTwo = (Map) resList.get(3);
		Map tablefield = (Map) resList.get(4);

		try {
			FileOutputStream out = new FileOutputStream(path + "\\" + txtName + ".txt");
			out.write("----数据库表区别----".getBytes());
			out.write("\r\n\r\n".getBytes());
			// 表结构差异,one为主
			for (int i = 0; i < diffTableOne.size(); i++) {
				String res = ("数据库[" + dbNameOne + "]-->表[" + diffTableOne.get(i).toString().toUpperCase() + "]在-->[" + dbNameTwo + "]中无法找到");
				out.write(res.getBytes());
				out.write("\r\n".getBytes());
			}

			// 表结构差异,Two为主
			for (int i = 0; i < diffTableTwo.size(); i++) {
				String res = ("数据库[" + dbNameTwo + "]-->表[" + diffTableTwo.get(i).toString().toUpperCase() + "]在库-->[" + dbNameOne + "]中无法找到");
				out.write(res.getBytes());
				out.write("\r\n".getBytes());
			}
			out.write("\r\n".getBytes());
			out.write("----数据库字段区别----".getBytes());
			out.write("\r\n\r\n".getBytes());
			Object One[] = mapOne.keySet().toArray();
			for (int i = 0; i < One.length; i++) {
				if (mapOne.get(One[i].toString()) != null) {
					List list = (List) mapOne.get(One[i].toString());
					for (int j = 0; j < list.size(); j++) {
						String res = ("数据库[" + dbNameOne + "]-->表[" + One[i].toString().toUpperCase() + "]-->字段[" + list.get(j) + "]在库-->[" + dbNameTwo + "]中无法找到");
						out.write(res.getBytes());
						out.write("\r\n".getBytes());
					}
				}
			}
			Object Two[] = mapTwo.keySet().toArray();
			for (int i = 0; i < Two.length; i++) {
				if (mapTwo.get(Two[i].toString()) != null) {
					List list = (List) mapTwo.get(Two[i].toString());
					for (int j = 0; j < list.size(); j++) {
						String res = ("数据库[" + dbNameTwo + "]-->表[" + Two[i].toString().toUpperCase() + "]-->字段[" + list.get(j) + "]在库-->[" + dbNameOne + "]中无法找到");
						out.write(res.getBytes());
						out.write("\r\n".getBytes());
					}
				}
			}
			out.write("\r\n".getBytes());
			out.write("----数据库字段属性区别----".getBytes());
			out.write("\r\n\r\n".getBytes());
			Object table[] = tablefield.keySet().toArray();
			for (int i = 0; i < table.length; i++) {
				Map map = (Map) tablefield.get(table[i].toString());
				if (map != null) {
					Object fieldAppr[] = map.keySet().toArray();
					for (int j = 0; j < fieldAppr.length; j++) {
						fieldAppr[j].toString();// 字段名
						List Appr = (List) map.get(fieldAppr[j]);// 属性List
						for (int k = 0; k < Appr.size(); k++) {
							Map attr = (Map) Appr.get(k);
							Object attrName[] = attr.keySet().toArray();// attrName存放的是字段的属性名
							for (int m = 0; m < attrName.length; m++) {
								List list = (List) attr.get(attrName[m]);// 拿到属性值list第一个是one表的第二个是two表的
								String res = ("数据库[" + dbNameOne + "]-->表[" + table[i].toString().toUpperCase() + "]-->字段[" + fieldAppr[j].toString() + "]-->属性[" + attrName[m]
										+ "]值[" + list.get(0).toString().toUpperCase() + "]与[" + dbNameTwo + "]库值[" + list.get(1).toString().toUpperCase() + "]不匹配");
								out.write(res.getBytes());
								out.write("\r\n".getBytes());
							}
						}
					}
				}
			}
			out.flush();
			out.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	@SuppressWarnings("unchecked")
	public List compareDB(Map dbOne, Map dbTwo) {
		Object tableNameOne[] = dbOne.keySet().toArray();
		Object tableNameTwo[] = dbTwo.keySet().toArray();
		String attr[] = { "COLUMN_TYPE", "IS_NULLABLE", "COLUMN_KEY" };
		List diffTableOne = new ArrayList();// 存放以one为主的差异表名
		List diffTableTwo = new ArrayList();// 存放以Two为主的差异表名
		Map mapOne = new HashMap();// key,表名。value此表冲突字段list
		Map mapTwo = new HashMap();// key,表名。value此表冲突字段list
		Map tablefield = new HashMap();// 数据库属性区别
		List diffFieldOne = null;
		List diffFieldTwo = null;
		List attrRes = null;// 冲突属性结果
		Map fieldAttr = null;// key,字段名.value此字段冲突属性list

		int flag = 0;
		for (int i = 0; i < tableNameOne.length; i++) {
			fieldAttr = new HashMap();

			for (int j = 0; j < tableNameTwo.length; j++) {
				if (tableNameOne[i].toString().equals(tableNameTwo[j].toString())) {// 如果表名相同
					diffFieldOne = new ArrayList();
					diffFieldTwo = new ArrayList();
					List fieldOne = (List) dbOne.get(tableNameOne[i].toString()); // List中是一个一个的Map，值是字段名，value是属性list
					List fieldTwo = (List) dbTwo.get(tableNameTwo[j].toString()); // List中是一个一个的Map，值是字段名，value是属性list
					for (int k = 0; k < fieldOne.size(); k++) {
						attrRes = new ArrayList();
						boolean fileFlag = false;
						Map mO = (Map) fieldOne.get(k);
						Object objOne[] = mO.keySet().toArray();// list中第一个map的key数组(其实map中只有一个key)
						String one = objOne[0].toString();// 字段名

						for (int n = 0; n < fieldTwo.size(); n++) {
							Map mT = (Map) fieldTwo.get(n);
							Object objTwo[] = mT.keySet().toArray();
							String two = objTwo[0].toString();// 字段名
							if (one.equals(two)) {// 如果两个字段名相等
								List attrOne = (List) mO.get(one);// One表属性List，格式[bigint(20),
								// NO, PRI]
								List attrTwo = (List) mT.get(two);// Two表属性List，格式[bigint(20),
								// NO, PRI]

								for (int m = 0; m < attrOne.size(); m++) {
									boolean attrFlag = false;
									if (attrOne.get(m).toString().equals(attrTwo.get(m).toString())) {
										attrFlag = true;
									}
									if (!attrFlag) {
										Map map = new HashMap();
										List list = new ArrayList();
										list.add(attrOne.get(m));
										list.add(attrTwo.get(m));
										map.put(attr[m], list);// key属性名，属性list，第一个是one表第二个是two表
										attrRes.add(map);
									}
								}
								fileFlag = true;
							}
						}
						fieldAttr.put(one, attrRes);// 字段名，属性list {BUILD_ID=[],
						// TEMPLATE_ID=[IS_NULLABLE]}
						if (!fileFlag) {
							diffFieldOne.add(one);
						}
					}
					tablefield.put(tableNameOne[i].toString(), fieldAttr);// 表名，map
					mapOne.put(tableNameOne[i].toString(), diffFieldOne);

					for (int k = 0; k < fieldTwo.size(); k++) {
						boolean fileFlag = false;
						Map mT = (Map) fieldTwo.get(k);
						Object objTwo[] = mT.keySet().toArray();
						String two = objTwo[0].toString();
						for (int n = 0; n < fieldOne.size(); n++) {
							Map mO = (Map) fieldOne.get(n);
							Object objOne[] = mO.keySet().toArray();
							String one = objOne[0].toString();
							if (two.equals(one)) {
								fileFlag = true;
								break;
							}
						}
						if (!fileFlag) {
							diffFieldTwo.add(two);
						}
					}
					mapTwo.put(tableNameOne[i].toString(), diffFieldTwo);

					flag = 1;
					break;
				}
			}

			if (flag == 0) {
				diffTableOne.add(tableNameOne[i]);
			}
			flag = 0;
		}

		for (int i = 0; i < tableNameTwo.length; i++) {
			for (int j = 0; j < tableNameOne.length; j++) {
				if (tableNameTwo[i].toString().equals(tableNameOne[j].toString())) {
					flag = 1;
					break;
				}
			}
			if (flag == 0) {
				diffTableTwo.add(tableNameTwo[i]);
			}
			flag = 0;
		}
		List resList = new ArrayList();
		resList.add(diffTableOne);
		resList.add(diffTableTwo);
		resList.add(mapOne);
		resList.add(mapTwo);
		resList.add(tablefield);
		return resList;
	}

	public static void main(String[] args) {
		try {
			System.out.println(Class.forName("com.kingree.jdbc.DBUtiles").getClass().getName());
			System.out.println(DBUtiles.class.getPackage().getName());
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		DBUtiles j = new DBUtiles();
		j.Swing();
		System.err.println();
	}
}
